﻿CREATE TRIGGER [dbo].[TR_V_MembershipRolePower_IUD]
   ON  [dbo].[V_MembershipRolePower]
   INSTEAD OF INSERT, UPDATE, DELETE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @PowerId INT;
	DECLARE @RoleId INT;

	DECLARE updateCursor CURSOR FOR  
	SELECT 
		COALESCE(i.PowerId,d.PowerId) AS PowerId,
		COALESCE(i.RoleId,d.RoleId) AS RoleId
	FROM INSERTED i
	FULL OUTER JOIN DELETED d ON i.PowerId = d.PowerId AND i.RoleId = d.RoleId
	
	OPEN updateCursor
	FETCH NEXT FROM updateCursor INTO @PowerId, @RoleId

	WHILE @@FETCH_STATUS = 0   
	BEGIN   
		--PRINT 'V_MembershipRolePower Update for PowerId = ' + CAST(@PowerId AS VARCHAR(max)) + ', RoleId = ' + CAST(@RoleId AS VARCHAR(max));

		IF EXISTS(SELECT * FROM dbo.MembershipRolePower rp WHERE @RoleId= rp.RoleId AND @PowerId= rp.PowerId)
		BEGIN
			UPDATE dbo.MembershipRolePower SET 
				AddFlag  = COALESCE((SELECT TOP 1 i.AddFlag  FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0), 
				DelFlag  = COALESCE((SELECT TOP 1 i.DelFlag  FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0), 
				EditFlag = COALESCE((SELECT TOP 1 i.EditFlag FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0), 
				ExecFlag = COALESCE((SELECT TOP 1 i.ExecFlag FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0), 
				GetFlag  = COALESCE((SELECT TOP 1 i.GetFlag  FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0)
			WHERE @RoleId= RoleId AND @PowerId= PowerId 
		END
		ELSE
		BEGIN
			INSERT dbo.MembershipRolePower
			        ( PowerId ,
			          RoleId ,
			          GetFlag ,
			          AddFlag ,
			          EditFlag ,
			          DelFlag ,
			          ExecFlag
			        )
			VALUES  ( @PowerId, -- PowerId - int
			          @RoleId , -- RoleId - int
			          COALESCE((SELECT TOP 1 i.GetFlag   FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0),
			          COALESCE((SELECT TOP 1 i.AddFlag   FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0),
			          COALESCE((SELECT TOP 1 i.EditFlag  FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0),
			          COALESCE((SELECT TOP 1 i.DelFlag   FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0),
			          COALESCE((SELECT TOP 1 i.ExecFlag  FROM INSERTED i WHERE @RoleId= i.RoleId AND @PowerId= i.PowerId), 0)
			        )
		END

	    FETCH NEXT FROM updateCursor INTO @PowerId, @RoleId
	END   
	CLOSE updateCursor
	DEALLOCATE updateCursor

END
